﻿using System;
using SmartSiteManagementSystem.IRepository.Admin;
using SmartSiteManagementSystem.Models.Admin;
using SmartSiteManagementSystem.Repository.DBHelper;
using System.Data;
using Dapper;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Linq;
using MySql.Data.MySqlClient;

namespace SmartSiteManagementSystem.Repository.Admin
{
    public class AdminPageBtnInfoRepository : IAdminPageBtnInfoRepository
    //,IDisposable
    {
        #region dataTable
        /*
create table `admin_pagebtn`(
	`btn_id`  int not null auto_increment primary key,
	`client_id` nvarchar(128) not null,
	`btn_type` smallint not null,
	`btn_name` varchar(50)  not null,
	`rel_url` varchar(5000)  not null,
	`sort_no` int  not null,
	`is_stop` bit not null default 0,
	`is_deleted`  bit not null default 0,
	`admin_menu_id` int not null,
    `create_time` datetime not null default now(),
	`create_man` varchar(20) not null
    )default charset=utf8;
         */
        #endregion
        //private IDbConnection _readConn;
       // private IDbConnection _writeConn;

        public bool Delete(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public AdminPageBtnInfo Get(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public int GetCount(Hashtable param)
        {
            throw new NotImplementedException();
        }

        public IList<AdminPageBtnInfo> GetList(Hashtable param, string orderBy, int first, int rows)
        {
            IList<AdminPageBtnInfo> back = null;
            if (param == null)
                param = new Hashtable();
            String querySql = @"select btn_id as BtnId,client_id  as ClientId,btn_type as BtnType,btn_name as BtnName,rel_url as RelUrl,sort_no as SortNo,is_stop as StopFlag,is_deleted as DeleteFlag,admin_menu_id as AdminMenuId,create_time as CreateTime,create_man as CreateMan from admin_pagebtn";
            using (IDbConnection _readConn = new MySqlConnection(DbHelperOptions.ReadConnectionString))
            {
                #region
                DynamicParameters pars = new DynamicParameters();
                StringBuilder whereCondition = new StringBuilder();
                if (param.Count > 0)
                {
                    whereCondition.Append(" where 1=1");
                    if (param.ContainsKey("btn_id"))
                    {
                        pars.Add("btn_id", param["btn_id"]);
                        whereCondition.Append("and btn_id =@btn_id");
                    }
                    if (param.ContainsKey("client_id"))
                    {
                        pars.Add("client_id", param["client_id"]);
                        whereCondition.Append(" and client_id =@client_id");
                    }
                    if (param.ContainsKey("btn_type"))
                    {
                        pars.Add("btn_type", param["btn_type"]);
                        whereCondition.Append(" and btn_type =@btn_type");
                    }
                    if (param.ContainsKey("btn_name"))
                    {
                        pars.Add("btn_name", param["btn_name"]);
                        whereCondition.Append(" and btn_name =@btn_name");
                    }
                    if (param.ContainsKey("is_stop"))
                    {
                        pars.Add("is_stop", param["is_stop"]);
                        whereCondition.Append(" and is_stop =@is_stop");
                    }
                    if (param.ContainsKey("is_deleted"))
                    {
                        pars.Add("is_deleted", param["is_deleted"]);
                        whereCondition.Append(" and is_deleted =@is_deleted");
                    }
                    if (param.ContainsKey("admin_menu_id"))
                    {
                        pars.Add("admin_menu_id", param["admin_menu_id"]);
                        whereCondition.Append(" and admin_menu_id =@admin_menu_id");
                    }

                }
                if (string.IsNullOrEmpty(orderBy))
                {
                    whereCondition.Append(" order by sort_no asc");
                }
                else
                {
                    whereCondition.Append(" order by @orderBy");
                    pars.Add("orderBy", orderBy);
                }
                whereCondition.Append(" limit @first,@rows");
                pars.Add("first", first);
                pars.Add("rows", rows);
                #endregion
                string execSql = querySql + whereCondition.ToString();
                back = _readConn.Query<AdminPageBtnInfo>(execSql, pars).AsList<AdminPageBtnInfo>();
            }
            return back;
        }

        public IList<AdminPageBtnInfo> GetList(Hashtable param)
        {
            throw new NotImplementedException();
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="obj"></param>
        /// <returns></returns>
        public int Insert(AdminPageBtnInfo obj)
        {
            int back = 0;
            #region sql
            string sql = @"INSERT INTO admin_pagebtn(client_id,
btn_type,
btn_name,
rel_url,
sort_no,
is_stop,
is_deleted,
admin_menu_id,
create_time,
create_man)
VALUES
(
 @client_id,
@btn_type,
@btn_name,
@rel_url,
@sort_no,
@is_stop,
@is_deleted,
@admin_menu_id,
@create_time,
@create_man); select last_insert_id();";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                back = _writeConn.QueryFirst<int>(sql, new
                {
                    client_id = obj.ClientId,
                    btn_type = obj.BtnType,
                    btn_name = obj.BtnName,
                    rel_url = obj.RelUrl,
                    sort_no = obj.SortNo,
                    is_stop = obj.StopFlag,
                    is_deleted = obj.DeleteFlag,
                    admin_menu_id = obj.AdminMenuId,
                    create_time = obj.CreateTime,
                    create_man = obj.CreateMan
                });
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="param"></param>
        /// <returns></returns>
        public bool Update(AdminPageBtnInfo obj)
        {
            bool back = false;
            #region sql
            string sql = @"UPDATE admin_pagebtn
SET client_id= @client_id,
btn_type = @btn_type,
btn_name = @btn_name,
rel_url = @rel_url,
sort_no = @sort_no,
is_stop= @is_stop,
is_deleted =@is_deleted,
admin_menu_id = @admin_menu_id,
create_time = @create_time,
create_man = @create_man
WHERE btn_id = @btn_id;";
            #endregion
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {

                int backint = _writeConn.Execute(sql, new
                {
                    btn_id = obj.BtnId,
                    client_id = obj.ClientId,
                    btn_type = obj.BtnType,
                    btn_name = obj.BtnName,
                    rel_url = obj.RelUrl,
                    sort_no = obj.SortNo,
                    is_stop = obj.StopFlag,
                    is_deleted = obj.DeleteFlag,
                    admin_menu_id = obj.AdminMenuId,
                    create_time = obj.CreateTime,
                    create_man = obj.CreateMan
                });
                if (backint > 0)
                {
                    back = true;
                }
            }
            return back;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="adminMenuId"></param>
        /// <param name="mbcList"></param>
        /// <param name="btnList"></param>
        /// <returns></returns>
        public bool UpdateByTran(int adminMenuId, IList<AdminPageBtnInfo> mbcList, IList<Hashtable> btnList)
        {
            string ids = "0";
            int newId = 0;

            bool back = false;
            using (IDbConnection _writeConn = new MySqlConnection(DbHelperOptions.WriteConnectionString))
            {
                _writeConn.Open();
                using (IDbTransaction transaction = _writeConn.BeginTransaction())
                {
                    try
                    {
                        #region insertAdminPageBtn
                        foreach (AdminPageBtnInfo item in mbcList)
                        {
                            string insertAdminPageBtnSql = @"INSERT INTO admin_pagebtn(client_id,
btn_type,
btn_name,
rel_url,
sort_no,
is_stop,
is_deleted,
admin_menu_id,
create_time,
create_man)
VALUES
(
 @client_id,
@btn_type,
@btn_name,
@rel_url,
@sort_no,
@is_stop,
@is_deleted,
@admin_menu_id,
@create_time,
@create_man); select last_insert_id();";

                            newId = _writeConn.QueryFirst<int>(insertAdminPageBtnSql, new
                            {
                                client_id = item.ClientId,
                                btn_type = item.BtnType,
                                btn_name = item.BtnName,
                                rel_url = item.RelUrl,
                                sort_no = item.SortNo,
                                is_stop = item.StopFlag,
                                is_deleted = item.DeleteFlag,
                                admin_menu_id = item.AdminMenuId,
                                create_time = item.CreateTime,
                                create_man = item.CreateMan
                            }, transaction);
                            ids += "," + newId;
                        }
                        #endregion
                        #region UpdateAdminPageBtn
                        foreach (Hashtable item in btnList)
                        {
                            string updateAdminPageBtnSql = @"UPDATE admin_pagebtn
SET client_id= @ClientId,
btn_type = @BtnType,
btn_name = @BtnName,
rel_url = @RelUrl,
sort_no = @SortNo,
admin_menu_id = @AdminMenuId,
WHERE btn_id = @W_Id;";
                            _writeConn.Execute(updateAdminPageBtnSql, new
                            {
                                W_Id = item["W_Id"],
                                AdminMenuId = item["AdminMenuId"],
                                BtnName = item["BtnName"],
                                BtnType = item["BtnType"],
                                SortNo = item["SortNo"],
                                ClientId = item["ClientId"],
                                RelUrl = item["RelUrl"]
                            }, transaction);
                            ids += "," + item["W_Id"].ToString();
                        }
                        #endregion
                        string deleteAdminPageBtnSql = "delete from admin_pagebtn where admin_menu_id=@admin_menu_id and btn_id not in(@Ids)";
                        DynamicParameters deleteAdminPageBtnSqlPars = new DynamicParameters();
                        deleteAdminPageBtnSqlPars.Add("admin_menu_id", adminMenuId);
                        deleteAdminPageBtnSqlPars.Add("ids", ids);
                        _writeConn.Execute(deleteAdminPageBtnSql, deleteAdminPageBtnSqlPars, transaction);
                        transaction.Commit();
                        back = true;
                    }
                    catch
                    {
                        transaction.Rollback();
                        back = false;
                    }

                }
            }
            return back;
        }
    }
}

